/*
* Created on Nov 26, 2003
*
* To change the template for this generated file go to
* Window - Preferences - Java - Code Generation - Code and Comments
*/
package test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.Format;
import java.text.SimpleDateFormat;
import java.util.Date;
import nz.co.transparent.client.util.Constants;
import nz.co.transparent.client.db.PoolingDriverHandler;
/**
* @author johnz
*
* Purpose: check optimistic locking
* Check is based on transaction exception
* This test uses different connections for reading and updating
*
*/
public class TestOptimisticLocking3 {
/**
*
*/
public TestOptimisticLocking3() {
super();
}
private void go() {
PoolingDriverHandler databaseConnectionPool = new PoolingDriverHandler("client");
Connection connRead;
Connection connUpdate;
try {
// If AutoCommit is true then we are not using a transaction
// Changes to the database by other clients are immediately visible to this client
// If AutoCommit is false then we are running in transaction mode
// Changes to the database by other clients are visible after commit or rollback
connRead = DriverManager.getConnection(Constants.JDBC_URL);
connRead.setAutoCommit(true);
//connRead.setAutoCommit(false);
connUpdate = DriverManager.getConnection(Constants.JDBC_URL);
connUpdate.setAutoCommit(false);
} catch (SQLException se) {
System.out.println("Cannot get SQL connUpdateection");
return;
}
ResultSet rset = null;
Statement stmtRead = null;
Statement stmtUpdate = null;
String sql;
try {
stmtRead = connRead.createStatement();
sql = "select DateUpdated from Client";
sql += " where (ClientID=1);";
rset = stmtRead.executeQuery(sql);
rset.next();
Date oldDate = rset.getDate("DateUpdated");
System.out.println("Old date=" + oldDate.getTime());
System.out.println("Start other transaction now");
try {
Thread.sleep(5000);
} catch (InterruptedException ie) {
System.out.println("Sleep interrupted");
}
System.out.println("Continued after sleep");
Format formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String date = formatter.format(new Date());
sql = "update Client";
sql += " set DateUpdated='" + date + "'";
sql += " where (ClientID=1);";
stmtUpdate = connUpdate.createStatement();
stmtUpdate.execute(sql);
connUpdate.commit();
System.out.println("After connUpdate.commit()");
if (connRead.getAutoCommit() == false) {
connRead.commit();
}
System.out.println("After connRead.commit()");
sql = "select DateUpdated from Client";
sql += " where (ClientID=1);";
rset = stmtRead.executeQuery(sql);
rset.next();
Date newDate = rset.getDate("DateUpdated");
System.out.println("New date=" + newDate.getTime());
} catch (SQLException se) {
System.out.println("SQL failed: " + se.getMessage());
try {
connUpdate.rollback();
System.out.println("Transaction rollback");
} catch (SQLException se2) {
System.out.println("Rollback failed: " + se2.getMessage());
}
}
try {
stmtRead.close();
connUpdate.close();
} catch (SQLException se) {
System.out.println("Close failed: " + se.getMessage());
}
System.out.println("TestOptimisticLocking ready");
}
public static void main(String[] args) {
new TestOptimisticLocking3().go();
}
}